Navigating the PostgreSQL Seas: A Quest To Hidden Treasures

AFUP Day Lyon
2024-05-24

Who am I

• Lætitia Avrot
• PostgreSQL Europe Treasurer
• #PostgresWomen Founder
• Practice Leader, Postgres & Security at EDB
• @l_avrot
• mydbanotebook.org / psql-tips.org

The treasure map

• Small gems
• Text Quoting
• Pattern matching
• Complex datatypes handling
• Upsert/MERGE

Small Gems

DDL is transactionnal

laetitia=# create table test(id integer generated always as identity);
CREATE TABLE

laetitia=# table test;
 id 
----
(0 rows) 

DDL is transactionnal

laetitia=# begin;
BEGIN

laetitia=*# drop table test;
DROP TABLE

laetitia=*# table test;
2024-04-11 13:05:20.589 CEST [18422] ERROR:  relation "test" does not exist at character 7
2024-04-11 13:05:20.589 CEST [18422] STATEMENT:  table test;
ERROR:  relation "test" does not exist
LINE 1: table test;
              ^

laetitia=!# rollback;
ROLLBACK

laetitia=# table test;
 id 
----
(0 rows)

Comments

COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures';

COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus';

COMMENT ON DATABASE my_database IS 'Development Database';

COMMENT ON ROLE my_role IS 'Administration group for finance tables';

Specialized indexes

• multicolumns
• partial
• covering
• expression
• special ops
• b-tree, BRIN, hash, GiST, GIN, SP-GiST

Foreign Data Wrappers

Access ALL YOUR DATA (really, all your data, we are not joking) from EVERYWHERE to Postgres!

Server-side programming

• PL/Java
• PL/Python
• PL/R
• PL/pgSQL (like PL/SQL)
• PL/Ruby
• PL/Scheme
• PL/sh
• PL/Tcl
• PL/v8 (JavaScript)
• SPI (C)

Your precious ship: text quoting

Text quoting: "

laetitia=# create table select (id integer);
ERROR:  syntax error at or near "select"
LINE 1: create table select (id integer);
                     ^

laetitia=# create table "select" (id integer);
CREATE TABLE

Text quoting: "

laetitia=#
select * from "select";
 id 
----
(0 rows)

laetitia=# select * from select;
ERROR:  syntax error at or near "select"
LINE 1: select * from select;
                      ^

Proper text quoting: '

laetitia=#
values ("test");
ERROR:  column "test" does not exist
LINE 1: values ("test");
                ^

laetitia=# values ('test');
 column1 
---------
 test
(1 row)

Text quoting: escaping

laetitia=#
values (E'They exclaimed, \'There are three different types of quotes:\n
 single quotes ( \' ),\n
 double quotes ( \" ),\n
 and backticks ( \` ).\'');
                           column1                            
--------------------------------------------------------------
 They exclaimed, 'There are three different types of quotes: +
  single quotes ( ' ),                                       +
  double quotes ( " ),                                       +
  and backticks ( ` ).'
(1 row)

laetitia=# values ('He whispered, ''The password is ''swordfish''.''');
                   column1                    
----------------------------------------------
 He whispered, 'The password is 'swordfish'.'
(1 row)

Dollar-Quoted String

laetitia=# values($$They exclaimed, There are three different types of quotes:
single quotes ( ' ),
double quotes ( " ),
and backticks ( ` ).'$$);
                          column1                           
------------------------------------------------------------
 They exclaimed, There are three different types of quotes:+
  single quotes ( ' ),                                     +
  double quotes ( " ),                                      +
  and backticks ( ` ).'
(1 row)

laetitia=# values (
  $Sentence$He whispered, 'The password is 'swordfish'.'$Sentence$);
                   column1                    
----------------------------------------------
 He whispered, 'The password is 'swordfish'.'
(1 row)

Your straw hat: text searching

Like and ilike

• like (with or without _ or %)
• ilike
• ~~ and ~~*
• !~~ and !~~*

Similar to

string SIMILAR TO pattern [ESCAPE escape-character]

string NOT SIMILAR TO pattern [ESCAPE escape-character]

Similar to

• | for alternation
• *, +, ?, {m,n} for repetition
• () for groups
• [] for a character class

Proper regular expressions

• ~ operator

'thomas' ~ 't.*ma' → t

• ~* operator

'thomas' ~ 'T.*ma' → t

Proper regular expressions

• !~ operator

'thomas' !~ 't.*max' → t

• !~* operator

'thomas' !~* 'T.*ma' → f

Regular expression functions

Your crew: complex data types handling

JSON

SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';
          

JSON path

• $.track.segments: retrieves the available track segments
• $.track.segments[*].location: retrieves the contents of an array
• $.track.segments[1].location: returns the coordinates of the first segment only
• ? (condition): filters

JSON path

$.track.segments[*] ?
 (@.location[1] < 13.4).HR ?
  (@ > 130)

First filters all segments by location, and then returns high heart rate values for these segments, if available

JSON Tables

• Query JSON data in SQL
• Not in Postgres 15
• Not in Postgres 16
• Hopefully in postgres 17 🤞

Geographical data

• PostGIS
• Mix of spatial queries and traditional SQL
• "What are the cities within 150 KM of Boston with the 10 lowest medium home prices?"

Geographical data

select name,
  medium_hval,
  location
from interesting_cities
where (
  select ST_Distance (
      ST_Transform (location, 3587),
      ST_Transform( (
        select location
        from interesting_cities
        where name = 'Boston'), 3587)
  ) ) < 150000
order by medium_hval
limit 10;

Geographical data

The one piece: Upserts/MERGE

Inserts tricks

• on conflict
• returning
• CTEs

Returning

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

Upsert

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

Merge

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

Thank you!